﻿'Imports System.Data
'Imports System.Data.SqlClient
Imports Microsoft.SqlServer.Dts.Runtime
Imports Microsoft.SqlServer.Dts.Pipeline
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper

<DtsPipelineComponent(DisplayName:="PipeLine XML (NCL)", _
                      Description:="PipeLine XML (NCL SQL2008)", _
                      ComponentType:=ComponentType.Transform, _
                      IconResource:="NCL.SSIS.DataFlow.PipeLineXML.ico")> _
Public Class PipeLineXMLSQL2008
    Inherits PipelineComponent

    Private _iInputColumns() As Integer
    Private _iOutputColumns() As Integer
    Private _sInputColumnNames() As String
    Private _iXMLColumn As Integer
    Private _bIncludeAllFieldsFlag As Boolean = True
    Private _sRowIdentifierString As String = "XMLRow"
    Private _bBuildFullXMLFlag As Boolean = False
    Private _sRootTagString As String = "Root"
    Private _sBaseOpenTagString As String = ""
    Private _sBaseCloseTagString As String = ""

    Public Overrides Sub ProvideComponentProperties()
        'Add Input
        Dim _oInput As IDTSInput100 = ComponentMetaData.InputCollection.New
        _oInput.Name = "Input"

        'Add Output
        Dim _oOutput As IDTSOutput100 = ComponentMetaData.OutputCollection.New
        _oOutput.Name = "Output"
        _oOutput.SynchronousInputID = _oInput.ID
        _oOutput.ExclusionGroup = 0

        'Add new column
        Dim _oOutColumn As IDTSOutputColumn100 = _oOutput.OutputColumnCollection.New
        Dim _oExColumn As IDTSExternalMetadataColumn100 = _oOutput.ExternalMetadataColumnCollection.New

        _oOutColumn.Name = "XMLData"
        _oOutColumn.SetDataTypeProperties(Microsoft.SqlServer.Dts.Runtime.Wrapper.DataType.DT_TEXT, 0, 0, 0, 1252)
        _iXMLColumn = _oOutColumn.ID

        'Build custom properties
        _sRowIdentifierString = "XMLRow"
        _bBuildFullXMLFlag = False
        _sRootTagString = "Root"

        Dim _oIncludeAllFieldsFlag As IDTSCustomProperty100 = ComponentMetaData.CustomPropertyCollection.New
        _oIncludeAllFieldsFlag.Name = "IncludeAllFieldsFlag"
        _oIncludeAllFieldsFlag.Description = "Flag indicating whether to include all available fields from input in XML output."
        _oIncludeAllFieldsFlag.Value = CType(_bIncludeAllFieldsFlag, Boolean)

        Dim _oRowIdentifierString As IDTSCustomProperty100 = ComponentMetaData.CustomPropertyCollection.New
        _oRowIdentifierString.Name = "RowIdentifierString"
        _oRowIdentifierString.Description = "Text used to specify a row of XML data, default: XMLRow"
        _oRowIdentifierString.Value = CType(_sRowIdentifierString, String)

        Dim _oBuildFullXMLFlag As IDTSCustomProperty100 = ComponentMetaData.CustomPropertyCollection.New
        _oBuildFullXMLFlag.Name = "BuildFullXMLFlag"
        _oBuildFullXMLFlag.Description = "Flag indicating whether a full XML statement should be built, including Root tags, otherwise an XML fragment will be built."
        _oBuildFullXMLFlag.Value = CType(_bBuildFullXMLFlag, Boolean)

        'MyBase.ProvideComponentProperties()
    End Sub

    Public Overrides Sub ReinitializeMetaData()
        If ComponentMetaData.AreInputColumnsValid = False Then
            ComponentMetaData.RemoveInvalidInputColumns()
        End If

        MyBase.ReinitializeMetaData()
    End Sub

    Public Overrides Sub PreExecute()
        Dim _oInput As IDTSInput100 = ComponentMetaData.InputCollection(0)
        Dim _oOutput As IDTSOutput100 = ComponentMetaData.OutputCollection(0)

        _bIncludeAllFieldsFlag = CType(ComponentMetaData.CustomPropertyCollection("IncludeAllFieldsFlag").Value, Boolean)
        _sRowIdentifierString = CType(ComponentMetaData.CustomPropertyCollection("RowIdentifierString").Value, String)
        _bBuildFullXMLFlag = CType(ComponentMetaData.CustomPropertyCollection("BuildFullXMLFlag").Value, Boolean)

        ReDim _iInputColumns(_oInput.InputColumnCollection.Count)
        ReDim _sInputColumnNames(_oInput.InputColumnCollection.Count)
        ReDim _iOutputColumns(_oOutput.OutputColumnCollection.Count)

        For _iCol As Integer = 0 To _oInput.InputColumnCollection.Count - 1
            Dim _oInputColumn As IDTSInputColumn100 = _oInput.InputColumnCollection(_iCol)
            _iInputColumns(_iCol) = BufferManager.FindColumnByLineageID(_oInput.Buffer, _oInputColumn.LineageID)
            _sInputColumnNames(_iCol) = _oInputColumn.Name
        Next

        For _iCol As Integer = 0 To _oOutput.OutputColumnCollection.Count - 1
            Dim _oOutputColumn As IDTSOutputColumn100 = _oOutput.OutputColumnCollection(_iCol)
            _iOutputColumns(_iCol) = BufferManager.FindColumnByLineageID(_oInput.Buffer, _oOutputColumn.LineageID)
        Next

        'Build base tags
        _sBaseOpenTagString = "<" _
                            & _sRowIdentifierString.Trim _
                            & " "

        _sBaseCloseTagString = "/>"

        If _bBuildFullXMLFlag = True Then
            _sBaseOpenTagString = "<" _
                                & _sRootTagString.Trim _
                                & ">" _
                                & _sBaseOpenTagString

            _sBaseCloseTagString = _sBaseCloseTagString _
                                 & "</" _
                                 & _sRootTagString.Trim _
                                 & ">"
        End If

        MyBase.PreExecute()
    End Sub

    Public Overrides Sub ProcessInput(ByVal inputID As Integer, ByVal buffer As Microsoft.SqlServer.Dts.Pipeline.PipelineBuffer)
        Dim _sWK As String
        Dim _oEnc As New System.Text.ASCIIEncoding()

        While (buffer.NextRow())
            Dim _oSB As New System.Text.StringBuilder("")

            For x As Integer = 0 To _iInputColumns.Length - 1
                If buffer.IsNull(_iInputColumns(x)) = False Then
                    Dim _oColumnData As Object = buffer(_iInputColumns(x))

                    'WriteLog("Input Column LineageId: " & CStr(inputColumns(x)) & ", Name: " & inputColumnNames(x))

                    If _sInputColumnNames(x) = "" Then
                        If _oSB.Length = 0 Then
                            _oSB.Append(_sBaseOpenTagString)

                            _sWK = _sRowIdentifierString.Trim _
                                 & "Error=""No Columns Selected"" "

                            _oSB.Append(_sWK)
                            _oSB.Append(_sBaseCloseTagString)
                        Else
                            _oSB.Insert(0, _sBaseOpenTagString)
                            _oSB.Append(_sBaseCloseTagString)
                        End If

                        buffer.AddBlobData(_iXMLColumn, _oEnc.GetBytes(_oSB.ToString))
                    Else 'Column Name Is not blank
                        _sWK = _oColumnData.ToString.Trim
                        _sWK = _sWK.Replace(Chr(34), """")

                        _oSB.Append(_sInputColumnNames(x))
                        _oSB.Append("=""")
                        _oSB.Append(_sWK)
                        _oSB.Append(""" ")
                    End If

                    ' TODO: Modify the column data.
                    'buffer(inputColumns(x)) = columnData
                Else
                    _oSB.Append(_sInputColumnNames(x))
                    _oSB.Append("=""[NULL]"" ")
                End If
            Next
        End While

        'MyBase.ProcessInput(inputID, buffer)
    End Sub

    Public Overrides Sub OnInputPathAttached(ByVal inputID As Integer)
        MyBase.OnInputPathAttached(inputID)

        If _bIncludeAllFieldsFlag = True Then
            Dim _oVI As IDTSVirtualInput100 = ComponentMetaData.InputCollection(0).GetVirtualInput

            For Each _oVIC As IDTSVirtualInputColumn100 In _oVI.VirtualInputColumnCollection
                SetUsageType(inputID, _oVI, _oVIC.LineageID, DTSUsageType.UT_READONLY)
            Next
        End If
    End Sub

    Public Overrides Function SetUsageType(ByVal inputID As Integer, ByVal virtualInput As Microsoft.SqlServer.Dts.Pipeline.Wrapper.IDTSVirtualInput100, ByVal lineageID As Integer, ByVal usageType As Microsoft.SqlServer.Dts.Pipeline.Wrapper.DTSUsageType) As Microsoft.SqlServer.Dts.Pipeline.Wrapper.IDTSInputColumn100
        If _bIncludeAllFieldsFlag = True Then
            If usageType = DTSUsageType.UT_IGNORED Then
                'replace ignored with readonly, they cannot remove the field.
                usageType = DTSUsageType.UT_READONLY
            End If
        End If

        Return MyBase.SetUsageType(inputID, virtualInput, lineageID, usageType)
    End Function

    Public Overrides Function Validate() As Microsoft.SqlServer.Dts.Pipeline.Wrapper.DTSValidationStatus
        'Handle case of flag change where we might need to re-add columns.
        Dim _bWk As Boolean = CType(ComponentMetaData.CustomPropertyCollection("IncludeAllFieldsFlag").Value, Boolean)

        If _bWk = True And _bIncludeAllFieldsFlag = False Then
            _bIncludeAllFieldsFlag = True

            'Be sure all fields are included
            Dim _iID As Integer = ComponentMetaData.InputCollection(0).ID
            Dim _oVI As IDTSVirtualInput100 = ComponentMetaData.InputCollection(0).GetVirtualInput

            For Each _oVIC As IDTSVirtualInputColumn100 In _oVI.VirtualInputColumnCollection
                If _oVIC.UsageType = DTSUsageType.UT_IGNORED Then
                    SetUsageType(_iID, _oVI, _oVIC.LineageID, DTSUsageType.UT_READONLY)
                End If
            Next
        ElseIf _bWk = False And _bIncludeAllFieldsFlag = True Then
            _bIncludeAllFieldsFlag = False
        End If

        Return MyBase.Validate()
    End Function

    Public Overrides Sub DeleteInput(ByVal inputID As Integer)
        Throw New Exception("Can't Delete Input " + inputID.ToString(System.Globalization.CultureInfo.InvariantCulture))
    End Sub

    Public Overrides Sub DeleteOutput(ByVal outputID As Integer)
        Throw New Exception("Can't Delete Output " + outputID.ToString(System.Globalization.CultureInfo.InvariantCulture))
    End Sub

    Public Overrides Function InsertOutput(ByVal insertPlacement As Microsoft.SqlServer.Dts.Pipeline.Wrapper.DTSInsertPlacement, ByVal outputID As Integer) As Microsoft.SqlServer.Dts.Pipeline.Wrapper.IDTSOutput100
        Throw New Exception("Can't add output to the component.")
    End Function

    Public Overrides Sub DeleteOutputColumn(ByVal outputID As Integer, ByVal outputColumnID As Integer)
        Throw New Exception("Can't delete output column.")
    End Sub
End Class
